﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Sql;
using System.Data;
using System.Xml;
using System.Configuration;
using System.Data.SqlClient;
 
using System.Data.Common;
namespace DataProvider
{
    public static class SqlUtils
    {
        public static SqlDbType GetSqlDataType(Type dataType, int length)
        {
            if (dataType == typeof(String))
            {
                if (length < 4000)
                {
                    return SqlDbType.NVarChar;
                }
                return SqlDbType.NText;
            }
            else if (dataType == typeof(Int32))
            {
                return SqlDbType.Int;
            }
            else if (dataType == typeof(Int16))
            {
                return SqlDbType.Int;
            }
            else if (dataType == typeof(Int64))
            {
                return SqlDbType.BigInt;
            }
            else if (dataType == typeof(byte[]))
            {
                return SqlDbType.VarBinary;
            }
            else if (dataType == typeof(Guid))
            {
                return SqlDbType.UniqueIdentifier;
            }
            else if (dataType == typeof(float))
            {
                return SqlDbType.Float;
            }
            else if (dataType == typeof(double))
            {
                return SqlDbType.Decimal;
            }
            else if (dataType == typeof(decimal))
            {
                return SqlDbType.Decimal;
            }
            else if (dataType == typeof(bool))
            {
                return SqlDbType.SmallInt;
            }
            return SqlDbType.VarChar;
        }

        public static List<String> GetSelectedFields(DataTable table)
        {
            if (table == null)
                return null;

            List<String> fields = new List<String>();
            foreach (DataColumn column in table.Columns)
            {
                fields.Add(column.ColumnName);
            }
            return fields;
        }

        public static List<String> GetSelectedFields(DbDataReader reader)
        {
            if (reader == null)
                return null;

            List<String> fields = new List<String>();
            int fieldCount = reader.VisibleFieldCount;
            for (int i = 0; i < fieldCount; i++)
            {
                fields.Add(reader.GetName(i));
            }

            return fields;
        }

        public static SqlParameter CreateSqlParameter(String paraName, SqlDbType dbType, Object value)
        {
            SqlParameter para = new SqlParameter(paraName, dbType);
            if (dbType == SqlDbType.Int
                || dbType == SqlDbType.SmallInt
                || dbType == SqlDbType.BigInt
                || dbType == SqlDbType.TinyInt)
            {
                int intValue;
                int.TryParse(value == null ? "0" : value.ToString(), out intValue);
                para.Value = intValue;
            }
            else if (dbType == SqlDbType.VarChar
                     || dbType == SqlDbType.NVarChar
                     || dbType == SqlDbType.Char
                     || dbType == SqlDbType.NChar
                     || dbType == SqlDbType.Text
                     || dbType == SqlDbType.NText)
            {
                if (value == null)
                {
                    para.Size = 2;
                    para.Value = DBNull.Value;
                }
                else if (value.ToString().Length==0)
                {
                    para.Size = 0;
                    para.Value ="";
                }
                else
                {
                    para.Size = value.ToString().Length;
                    if (para.Size == 0)
                        para.Size = 2;
                    para.Value = value.ToString();
                }
            }
            else if (dbType == SqlDbType.Float)
            {
                float intValue;
                float.TryParse(value == null ? "0" : value.ToString(), out intValue);
                para.Value = intValue;
            }
            else if (dbType == SqlDbType.Decimal)
            {
                decimal intValue;
                decimal.TryParse(value == null ? "0" : value.ToString(), out intValue);
                para.Value = intValue;
            }
            else if (dbType == SqlDbType.Real)
            {
                double intValue;
                double.TryParse(value == null ? "0" : value.ToString(), out intValue);
                para.Value = intValue;
            }
            else
            {
                if (value == null)
                {
                    para.Value = DBNull.Value;
                }
                else
                {
                    para.Value = value;
                }
            }
            return para;
        }

        public static String CreateSqlValue(String sqlValue, SqlDbType dbType)
        {
            if (dbType == SqlDbType.Int
                || dbType == SqlDbType.SmallInt
                || dbType == SqlDbType.BigInt
                || dbType == SqlDbType.TinyInt)
            {
                int intValue;
                int.TryParse(sqlValue == null ? "0" : sqlValue.ToString(), out intValue);
                return intValue.ToString();
            }
            if ( dbType == SqlDbType.VarChar
                 || dbType == SqlDbType.NVarChar
                 || dbType == SqlDbType.Char
                 || dbType == SqlDbType.NChar
                 || dbType == SqlDbType.Text
                 || dbType == SqlDbType.NText)
            {
                if (String.IsNullOrEmpty(sqlValue))
                {
                    return "''";
                }
                return "'" +EncodeUtils.EncodeSql(sqlValue) +"'";
            }
            if (dbType == SqlDbType.Float)
            {
                float intValue;
                float.TryParse(sqlValue == null ? "0" : sqlValue.ToString(), out intValue);
                return intValue.ToString();
            }
            if (dbType == SqlDbType.Decimal)
            {
                decimal intValue;
                decimal.TryParse(sqlValue == null ? "0" : sqlValue.ToString(), out intValue);
                return intValue.ToString();
            }
            if (dbType == SqlDbType.Real)
            {
                double intValue;
                double.TryParse(sqlValue == null ? "0" : sqlValue.ToString(), out intValue);
                return intValue.ToString();
            }
            return "'" + EncodeUtils.EncodeSql(sqlValue) + "'";
        }


    }
}
